import json
import logging
import uuid
import re
import sqlparse
import datetime

from utils import db_util
from controller import base_handler
from template import json_template, sql_template
from database import database_mgr

interface_field_list = (
    "id",
    "system_id",
    "module_id",
    "resource_id",
    "database_id",
    "schema_name",
    "table_name",
    "api_type",
    "api_action_type",
    "api_path",
    "api_name",
    "api_template",
    "api_desc",
    "api_status",
    "update_time",
    "id_field",
    "primary_fields",
    "required_fields",
    "like_fields",
)


class ListHandler(base_handler.BaseHandler):
    def get(self):
        page = self.get_query_argument("page", "1")
        perPage = self.get_query_argument("perPage", "100")
        orderBy = self.get_query_argument("orderBy", "update_time")
        orderDir = self.get_query_argument("orderDir", "desc")

        params = []
        whereSql = " where"
        for fieldName in (
                "system_id", "module_id", "resource_id", "database_id", "api_type", "api_action_type", "api_status"):
            fieldValue = self.get_query_argument(fieldName, None)
            if fieldValue:
                params.append(fieldValue)
                whereSql += " ci.%s = ? and" % fieldName

        api_path_name = self.get_query_argument("api_path_name", None)
        if api_path_name:
            params.append("%" + api_path_name + "%")
            params.append("%" + api_path_name + "%")
            whereSql += " (api_name like ? or api_path like ?) and"

        sql = """
        SELECT
          ci.*,
          cs.sys_code,
          cs.sys_name,
          cm.m_code,
          cm.m_name,
          cr.res_code,
          cr.res_name,
          cd.db_addr,
          cd.db_port,
          cd.db_name 
        FROM
          crud_interface ci
          LEFT JOIN crud_system cs ON cs.id = ci.system_id
          LEFT JOIN crud_module cm ON cm.id = ci.module_id
          LEFT JOIN crud_resource cr ON cr.id = ci.resource_id
          LEFT JOIN crud_database cd ON cd.id = ci.database_id
        """
        if len(params) > 0:
            sql += whereSql[0:-3]
        else:
            params = None

        sql += " order by %s %s" % (orderBy, orderDir)

        rows = db_util.execute_page(sql, int(page), int(perPage), params)

        res = {
            "status": 0,
            "message": "ok",
            "data": {"page": page, "total": rows["total"], "rows": rows["items"]},
        }

        self.set_response(res, 200)


class AddHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        res_body = json.loads(self.request.body.decode())
        api_id = res_body.get("id", None)
        if api_id is None:
            api_id = uuid.uuid4().hex
            res_body["id"] = api_id

        params = []
        field_names = []
        field_values = []
        for field_name in interface_field_list:
            field_value = res_body.get(field_name, None)
            if field_value:
                field_names.append(field_name)
                field_values.append("?")
                params.append(field_value)

        field_names.append("update_time")
        field_values.append("?")
        params.append(datetime.datetime.now().strftime("%Y%m%d%H%M%S"))

        sql = """ insert into crud_interface (%s)values(%s)""" % (
            ",".join(field_names),
            ",".join(field_values),
        )

        flag = db_util.execute_update(sql, params=params)
        if flag is True:

            res = {"status": 0, "message": "添加成功"}
        else:
            res = {"status": 1, "message": "添加失败:%s" % db_util.get_last_error()}

        if flag is True:
            apiActionType = res_body.get("api_action_type", None)
            if apiActionType in ("EDIT", "DELETE", "ROW"):
                id_field = res_body.get("id_field", None)
                if id_field:
                    add_param_verify([[api_id, id_field, ".+", "非空"]])

        self.set_response(res, 200)


class EditHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        res_body = json.loads(self.request.body.decode())
        m_id = res_body.get("id", None)
        param = []
        up_sql = ""
        for field_name in interface_field_list:
            field_value = res_body.get(field_name, None)
            if field_value:
                param.append(field_value)
                up_sql += "%s=?," % field_name

        param.append(datetime.datetime.now().strftime("%Y%m%d%H%M%S"))
        up_sql += "update_time=?,"

        param.append(m_id)

        sql = """update crud_interface set %s where id=?""" % (up_sql[0:-1])
        flag = db_util.execute_update(sql, param)
        if flag is True:
            res = {"status": 0, "message": "修改成功"}
        else:
            res = {"status": 1, "message": "修改失败:%s" % db_util.get_last_error()}

        self.set_response(res, 200)


class DelHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        res_body = json.loads(self.request.body.decode())
        flag = False
        api_id = res_body.get("id", None)

        if api_id and api_id != "":
            sql = "delete from crud_verify where interface_id =?)"
            db_util.execute_update(sql, [api_id])

            sql = "delete from crud_interface where id =?"
            flag = db_util.execute_update(sql, [api_id])
        if flag is True:
            res = {"status": 0, "message": "删除成功"}
        else:
            res = {"status": 1, "message": "删除失败:%s" % db_util.get_last_error()}

        self.set_response(res, 200)


class ParamListHandler(base_handler.BaseHandler):
    def get(self):
        interface_id = self.get_query_argument("id", None)
        if interface_id is None:
            res = {"status": 0, "message": "success", "data": []}
            self.set_response(res, 200)
            return

        sql = "select api_template from crud_interface where id =?"
        row = db_util.fetchone(sql, [interface_id])
        if row is None:
            res = {"status": 0, "message": "success", "data": []}
            self.set_response(res, 200)
            return

        params = []
        api_template = row[0]

        regex = "\\$\\{(.*?)\\}"
        pattern = re.compile(regex)
        results = pattern.findall(api_template)
        for param_name in results:
            params.append({"label": param_name, "value": param_name})

        regex = "\\#\\{(.*?)\\}"
        pattern = re.compile(regex)
        results = pattern.findall(api_template)
        for param_name in results:
            params.append({"label": param_name, "value": param_name})

        res = {"status": 0, "message": "success", "data": params}

        self.set_response(res, 200)


class FormatHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        res_body = json.loads(self.request.body.decode())
        format_sql = ""
        sql = res_body.get("api_template", None)
        if sql:
            format_sql = sqlparse.format(sql, reindent=True, keyword_case="lower")

        res = {"status": 0, "message": "success", "data": {"api_template": format_sql}}
        self.set_response(res, 200)


def add_param_verify(verify_list):
    sql = """insert into crud_verify (id,interface_id,verify_param,verify_regex,verify_desc,verify_status)values(?,?,?,?,?,?)"""
    for verify in verify_list:
        params = list()
        params.append(uuid.uuid1().hex)
        params.append(verify[0])
        params.append(verify[1])
        params.append(verify[2])
        params.append(verify[3])
        params.append(1)
        db_util.execute_update(sql, params=params)


def add_json_interface(res_body, api_action_type, api_action_desc):
    api_id = res_body.get("id", uuid.uuid1().hex)
    system_id = res_body.get("system_id", None)
    module_id = res_body.get("module_id", None)
    resource_id = res_body.get("resource_id", None)
    database_id = res_body.get("database_id", None)
    schema_name = res_body.get("schema_name", None)
    table_name = res_body.get("table_name", None).lower()
    id_field = res_body.get("id_field", "id").lower()
    primary_fields = res_body.get("primary_fields", "").lower()
    required_fields = res_body.get("required_fields", "").lower()
    like_fields = res_body.get("like_fields", "").lower()

    verify_list = []
    if api_action_type == "ADD":
        template = json_template.create_add_template(schema_name, table_name, id_field)
        verify_list.append([api_id, id_field, ".+", "非空"])
    elif api_action_type == "DELETE":
        template = json_template.create_del_template(schema_name, table_name, id_field)
        verify_list.append([api_id, id_field, ".+", "非空"])
    elif api_action_type == "EDIT":
        template = json_template.create_edit_template(schema_name, table_name, id_field)
        verify_list.append([api_id, id_field, ".+", "非空"])
    elif api_action_type == "ROW":
        template = json_template.create_row_template(schema_name, table_name, id_field)
        verify_list.append([api_id, id_field, ".+", "非空"])
    elif api_action_type == "LIST":
        template = json_template.create_list_template(schema_name, table_name)
    else:
        return False, "未支持的操作类型[%s]" % api_action_type
    template = json.dumps(template, ensure_ascii=False, sort_keys=False)
    params = [api_id, system_id, module_id, resource_id, database_id, schema_name, table_name,
              "JSON", api_action_type.upper(), api_action_type.lower(), api_action_desc, template,
              api_action_desc, 1, datetime.datetime.now().strftime("%Y%m%d%H%M%S"), id_field, primary_fields,
              required_fields, like_fields]

    values = ["?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?"]
    sql = """insert into crud_interface (%s)values(%s)""" % (",".join(interface_field_list), ",".join(values),)

    ret = db_util.execute_update(sql, params=params)
    if ret:
        add_param_verify(verify_list)
    return ret, db_util.get_last_error()


def add_sql_interface(res_body, api_action_type, api_action_desc):
    api_id = res_body.get("id", uuid.uuid1().hex)
    system_id = res_body.get("system_id", None)
    module_id = res_body.get("module_id", None)
    resource_id = res_body.get("resource_id", None)
    database_id = res_body.get("database_id", None)
    schema_name = res_body.get("schema_name", None).lower()
    table_name = res_body.get("table_name", None).lower()
    id_field = res_body.get("id_field", None).lower()
    primary_fields = res_body.get("primary_fields", None).lower()
    required_fields = res_body.get("required_fields", None).lower()
    like_fields = res_body.get("like_fields", None).lower()

    verify_list = []

    field_list_res = database_mgr.get_field_list(database_id, schema_name, table_name)
    field_list = field_list_res["data"]
    if api_action_type == "ADD":
        template = sql_template.create_add_template(schema_name, table_name, id_field, field_list)
        verify_list.append([api_id, id_field, ".+", "非空"])
    elif api_action_type == "DELETE":
        template = sql_template.create_del_template(schema_name, table_name, id_field)
        verify_list.append([api_id, id_field, ".+", "非空"])
    elif api_action_type == "EDIT":
        template = sql_template.create_edit_template(schema_name, table_name, id_field, field_list)
        verify_list.append([api_id, id_field, ".+", "非空"])
    elif api_action_type == "ROW":
        template = sql_template.create_row_template(schema_name, table_name, id_field)
        verify_list.append([api_id, id_field, ".+", "非空"])
    elif api_action_type == "LIST":
        template = sql_template.create_list_template(schema_name, table_name, like_fields, field_list)
    else:
        return False, "未支持的操作类型[%s]" % api_action_type

    params = [api_id, system_id, module_id, resource_id, database_id, schema_name, table_name,
              "SQL", api_action_type.upper(), api_action_type.lower(), api_action_desc, template,
              api_action_desc, 1, datetime.datetime.now().strftime("%Y%m%d%H%M%S"), id_field, primary_fields,
              required_fields, like_fields]

    values = ["?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?", "?"]
    sql = """insert into crud_interface (%s)values(%s)""" % (",".join(interface_field_list), ",".join(values),)

    ret = db_util.execute_update(sql, params=params)
    if ret:
        add_param_verify(verify_list)
    return ret, db_util.get_last_error()


class BatchAddHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        res_body = json.loads(self.request.body.decode())
        api_type = res_body.get("api_type", "JSON")
        api_oper_type_dict = {"ADD": "新增", "DELETE": "删除", "EDIT": "编辑", "LIST": "查询", "ROW": "详情"}
        res = {"status": 0, "message": "批量添加接口成功"}
        for api_oper_type in api_oper_type_dict.keys():
            api_oper_desc = api_oper_type_dict[api_oper_type]
            if api_type == "JSON":
                ret, desc = add_json_interface(res_body, api_oper_type, api_oper_desc)
                if ret is False:
                    res = {"status": 1,
                           "message": "添加[JSON]模板[%s:%s]接口失败[%s]" % (api_oper_type, api_oper_desc, desc)}
                    break
            elif api_type == "SQL":
                ret, desc = add_sql_interface(res_body, api_oper_type, api_oper_desc)
                if ret is False:
                    res = {"status": 1,
                           "message": "添加[SQL]模板[%s:%s]接口失败[%s]" % (api_oper_type, api_oper_desc, desc)}
                    break
            else:
                res = {"status": 1, "message": "未支持的模板类型[%s]" % api_type}

        self.set_response(res, 200)


class CreateTemplateHandler(base_handler.BaseHandler):
    def post(self):
        logging.info(self.request.body.decode())
        json_body = json.loads(self.request.body.decode())
        api_type = json_body.get("api_type", "JSON")
        api_action_type = json_body.get("api_action_type", None)
        database_id = json_body.get("database_id", None)
        schema_name = json_body.get("schema_name", None)
        table_name = json_body.get("table_name", "TABLE_NAME")
        id_field = json_body.get("id_field", "id")
        like_fields = json_body.get("like_fields", None)

        if api_type == "JSON":
            if api_action_type == "ADD":
                template = json_template.create_add_template(schema_name, table_name, id_field)
            elif api_action_type == "DELETE":
                template = json_template.create_del_template(schema_name, table_name, id_field)
            elif api_action_type == "EDIT":
                template = json_template.create_edit_template(schema_name, table_name, id_field)
            elif api_action_type == "ROW":
                template = json_template.create_row_template(schema_name, table_name, id_field)
            elif api_action_type == "LIST":
                template = json_template.create_list_template(schema_name, table_name)
            elif api_action_type == "SELECT":  # 单表查询
                template = json_template.create_select_template(schema_name, table_name)
            elif api_action_type == "TREE":  # 单表查询
                template = json_template.create_tree_template(schema_name, table_name)
            else:
                template = json_template.create_get_template()
        elif api_type == "SQL":
            filed_list_res = database_mgr.get_field_list(schema_name, table_name)
            filed_list = filed_list_res["data"]
            if api_action_type == "ADD":
                template = sql_template.create_add_template(schema_name, table_name, id_field, filed_list)
            elif api_action_type == "DELETE":
                template = sql_template.create_del_template(schema_name, table_name, id_field)
            elif api_action_type == "EDIT":
                template = sql_template.create_edit_template(schema_name, table_name, id_field, filed_list)
            elif api_action_type == "ROW":
                template = sql_template.create_row_template(schema_name, table_name, id_field)
            elif api_action_type == "LIST":
                template = sql_template.create_list_template(schema_name, table_name, like_fields, filed_list)
            elif api_action_type == "SELECT":  # 单表查询
                template = sql_template.create_select_template()
            elif api_action_type == "TREE":  # 单表查询
                template = sql_template.create_tree_template()
            else:
                template = sql_template.create_get_template()
        else:
            template = ""
        res = {
            "status": 0,
            "message": "success",
            "data": {"api_template": template},
        }
        self.set_response(res, 200)
